const sql = require('mssql');
const baseFn = require('../../public/base.js');
const fs = require('fs'); // 图片路径
const path = require('path'); // 图片路径
let uploadDir = './images/';
/**
 * 
 * 入职人员信息管理
 */

// 存储图片
const storagePic = (file, ind) => {
    let fileName = file.name;
    let newFileName = Date.now() + '_' + ind + fileName;
    let readStream = fs.createReadStream(file.path);
    let writeStream = fs.createWriteStream(uploadDir + newFileName);
    readStream.pipe(writeStream);
    return 'http://www.rsimg.com/' + newFileName
}


// 获取未审核的数量
const getUnAuditCount = async (ctx, next) => {

    let employeeUp = await sql.query(`SELECT COUNT(id) AS NUM FROM YG_JS WHERE status  = 'F'`);
    let payRise = await sql.query(`SELECT COUNT(id) AS NUM FROM YG_JX WHERE status  = 'F'`);
    let applyLeave = await sql.query(`SELECT COUNT(id) AS NUM FROM YG_QJJL WHERE status  = 'F'`);
    let sellingOff = await sql.query(`SELECT COUNT(id) AS NUM FROM YG_QJJL WHERE sellingOffStatus  = 'F' AND status = 'T'`);
    let turnFormal = await sql.query(`SELECT COUNT(id) AS NUM FROM YG_ZZ WHERE status  = 'F'`);

    ctx.body = {
        employeeUpAudit: employeeUp.recordset[0].NUM,
        payRiseAudit: payRise.recordset[0].NUM,
        applyLeaveAudit: applyLeave.recordset[0].NUM,
        sellingOff: sellingOff.recordset[0].NUM,
        turnFormalAudit: turnFormal.recordset[0].NUM
    }
}
/**
 * 
 * 保存入职人员登记信息
 */
const keepEntryRegistration = async (ctx, next) => {
    const files = ctx.request.files;
    const body = ctx.request.body;
    const userInfo = JSON.parse(body.userInfo);
    const addressInfo = JSON.parse(body.addressInfo);
    const memberInfo = JSON.parse(body.memberInfo);
    const skillInfo = JSON.parse(body.skillInfo);
    const schoolInfo = JSON.parse(body.schoolInfo);

    let picNameList = {};
    for (let item in files) {
        picNameList[item] = storagePic(files[item], item)
    }
    let lastResult = '';
    // 编辑入职人员信息
    if (Boolean(Number(body.id))) {
        // 更新用户的基本信息
        let updateUserInfoSql = `UPDATE YG_DA SET company='${userInfo.company.join(',')}',part='${userInfo.part.join(',')}',post='${userInfo.post.join(',')}',job='${userInfo.job.join(',')}',username='${userInfo.username}',sex='${userInfo.sex}',birth='${userInfo.birth}',nativeplace='${userInfo.nativePlace.join(',')}',phone='${userInfo.phone}',email='${userInfo.email}',qqnum='${userInfo.qqNum}',blood='${userInfo.blood}',marriage='${userInfo.marriage}',nation='${userInfo.nation}',face='${userInfo.face}',height='${userInfo.height}',emergecontact='${userInfo.emergeContact}',relation='${userInfo.relation}',emergeContactPhone='${userInfo.emergeContactPhone}',weight='${userInfo.weight}',languagelevel='${userInfo.languageLevel}',englishlevel='${userInfo.englishLevel}',speciality='${userInfo.speciality}' `
        if (picNameList.registPhoto) {
            updateUserInfoSql += `,registphoto='${picNameList.registPhoto}'`;
        }
        if (picNameList.identityCard) {
            updateUserInfoSql += `,identitycard='${picNameList.identityCard}'`;
        }
        updateUserInfoSql += `,idnum='${userInfo.idNum}',idvalide='${userInfo.idValide}',entrytime='${userInfo.entryTime}',registertime='${userInfo.registerTime}',wagecrrection='${userInfo.wageCrrection}',applicationsource='${userInfo.applicationSource}',recommender='${userInfo.recommender}' WHERE id = ${body.id}`;
        lastResult = await sql.query(updateUserInfoSql);

        // 更新住址信息
        let updateAddressInfoSql = `UPDATE YG_DZ SET familyAddress='${addressInfo.familyAddress.join(',')}',familyDetailAddress='${addressInfo.familyDetailAddress}',familyCode='${addressInfo.familyCode}',houseAddress='${addressInfo.houseAddress.join(',')}',houseDetailAddress='${addressInfo.houseDetailAddress}',houseCode='${addressInfo.houseCode}',nowAddress='${addressInfo.nowAddress.join(',')}',nowDetailAddress='${addressInfo.nowDetailAddress}',nowCode='${addressInfo.nowCode}' WHERE id =  ${body.id}`;
        await sql.query(updateAddressInfoSql);

        // 更新家庭成员信息
        let deleteMemberInfoSql = `DELETE FROM YG_JTCR WHERE id = ${body.id}`;
        await sql.query(deleteMemberInfoSql);
        let addMemberInfoSql = `INSERT INTO YG_JTCR (id,membername,memberage,memberrlationship,memberwork,memberpost,memberiscompany,ind) `;
        memberInfo.items.forEach((item, ind) => {
            if (ind === memberInfo.items.length - 1) {
                addMemberInfoSql += `SELECT ${body.id} , '${item.memberName}', ${item.memberAge}, '${item.memberRlationship}', '${item.memberWork}', '${item.memberPost}', '${item.memberIsCompany}' , ${item.index} `;
            } else {
                addMemberInfoSql += `SELECT ${body.id} , '${item.memberName}', ${item.memberAge}, '${item.memberRlationship}', '${item.memberWork}', '${item.memberPost}', '${item.memberIsCompany}' , ${item.index} UNION `;
            }
        });
        await sql.query(addMemberInfoSql);

        // 更新职业技能信息
        let deleteSkillInfoSql = `DELETE FROM YG_ZCJL WHERE id = ${body.id}`;
        await sql.query(deleteSkillInfoSql);
        let addSkillInfoSql = `INSERT INTO YG_ZCJL (id,skillname,skilllevel,skilltype,skillTime,ind) `;
        skillInfo.items.forEach((item, ind) => {
            if (ind === skillInfo.items.length - 1) {
                addSkillInfoSql += ` SELECT ${body.id} , '${item.skillName}', '${item.skillLevel}', '${item.skillType}', '${item.skillTime}' , ${item.index} `;
            } else {
                addSkillInfoSql += ` SELECT ${body.id} , '${item.skillName}', '${item.skillLevel}', '${item.skillType}', '${item.skillTime}' , ${item.index} UNION `;
            }
        });
        await sql.query(addSkillInfoSql);

        // 更新学历信息
        schoolInfo.items.forEach(async (item, ind) => {

            if (Object.keys(files).includes('schoolInfo' + item.index)) {
                let delteSchoolInfoSql = `DELETE FROM YG_XL WHERE id = ${body.id} AND ind = ${item.index}`;
                await sql.query(delteSchoolInfoSql);
                let picName = item.idPhoto.slice(item.idPhoto.lastIndexOf("\/"), item.length);
                fs.unlink(uploadDir + picName, () => { })
                let addschoolInfoSql = `INSERT INTO YG_XL (id,schooleduction,schoolname,schoolspecial,graduationtime,schoolstyle,idphoto,ind)  SELECT ${body.id} , '${item.schoolEduction}', '${item.schoolName}', '${item.schoolSpecial}', '${item.graduationTime}', '${item.schoolStyle}', '${picNameList['schoolInfo' + item.index]}', ${item.index} `;
                await sql.query(addschoolInfoSql);
            } else {
                let updateSchoolInfo = `UPDATE YG_XL SET schoolEduction ='${item.schoolEduction}' ,schoolName='${item.schoolName}',schoolSpecial='${item.schoolSpecial}',graduationTime='${item.graduationTime}',schoolStyle='${item.schoolStyle}' WHERE id = '${body.id}' AND ind = '${item.index}'`;
                await sql.query(updateSchoolInfo);
            }

        });




    } else {
        // 添加入职人员信息

        // 写入用户的基本信息
        let addUserInfoSql = `INSERT INTO YG_DA(company,part,post,job,username,sex,birth,nativeplace,phone,email,qqnum,blood,marriage,nation,face,height,emergecontact,relation,emergecontactphone,weight,languagelevel,englishlevel,speciality,registphoto,idnum,identitycard,idvalide,entrytime,registertime,wagecrrection,applicationsource,recommender,realWage) VALUES ('${userInfo.company}','${userInfo.part}','${userInfo.post}','${userInfo.job}','${userInfo.username}','${userInfo.sex}','${userInfo.birth}','${userInfo.nativePlace.join(',')}','${userInfo.phone}','${userInfo.email}','${userInfo.qqNum}','${userInfo.blood}','${userInfo.marriage}','${userInfo.nation}','${userInfo.face}','${userInfo.height}','${userInfo.emergeContact}','${userInfo.relation}','${userInfo.emergeContactPhone}','${userInfo.weight}','${userInfo.languageLevel}','${userInfo.englishLevel}','${userInfo.speciality}','${picNameList.registPhoto}','${userInfo.idNum}','${picNameList.identityCard}','${userInfo.idValide}','${userInfo.entryTime}','${userInfo.registerTime}','${userInfo.wageCrrection}','${userInfo.applicationSource}','${userInfo.recommender}','${userInfo.wageCrrection}')`;
        lastResult = await sql.query(addUserInfoSql);
        let idResult = await sql.query(`select @@IDENTITY AS ID`);
        let id = idResult.recordset[0].ID;
        // 写入住址信息
        let addAddressInfoSql = `INSERT INTO YG_DZ VALUES (${id},'${addressInfo.familyAddress.join(',')}','${addressInfo.familyDetailAddress}','${addressInfo.familyCode}','${addressInfo.houseAddress.join(',')}','${addressInfo.houseDetailAddress}','${addressInfo.houseCode}','${addressInfo.nowAddress.join(',')}','${addressInfo.nowDetailAddress}','${addressInfo.nowCode}')`;
        await sql.query(addAddressInfoSql);
        // 写入家庭成员信息
        let addMemberInfoSql = `INSERT INTO YG_JTCR (id,membername,memberage,memberrlationship,memberwork,memberpost,memberiscompany,ind) `;
        memberInfo.items.forEach((item, ind) => {
            if (ind === memberInfo.items.length - 1) {
                addMemberInfoSql += `SELECT ${id} , '${item.memberName}', ${item.memberAge}, '${item.memberRlationship}', '${item.memberWork}', '${item.memberPost}', '${item.memberIsCompany}' , ${item.index} `;
            } else {
                addMemberInfoSql += `SELECT ${id} , '${item.memberName}', ${item.memberAge}, '${item.memberRlationship}', '${item.memberWork}', '${item.memberPost}', '${item.memberIsCompany}' , ${item.index} UNION `;
            }
        });
        await sql.query(addMemberInfoSql);
        // 写入职业技能信息
        let addSkillInfoSql = `INSERT INTO YG_ZCJL (id,skillname,skilllevel,skilltype,skillTime,ind) `;
        skillInfo.items.forEach((item, ind) => {
            if (ind === skillInfo.items.length - 1) {
                addSkillInfoSql += ` SELECT ${id} , '${item.skillName}', '${item.skillLevel}', '${item.skillType}', '${item.skillTime}' , ${item.index} `;
            } else {
                addSkillInfoSql += ` SELECT ${id} , '${item.skillName}', '${item.skillLevel}', '${item.skillType}', '${item.skillTime}' , ${item.index} UNION `;
            }
        });
        await sql.query(addSkillInfoSql);
        // 写入学历信息
        let addschoolInfoSql = `INSERT INTO YG_XL (id,schooleduction,schoolname,schoolspecial,graduationtime,schoolstyle,idphoto,ind) `;
        schoolInfo.items.forEach((item, ind) => {
            if (ind === schoolInfo.items.length - 1) {
                addschoolInfoSql += ` SELECT ${id} , '${item.schoolEduction}', '${item.schoolName}', '${item.schoolSpecial}', '${item.graduationTime}', '${item.schoolStyle}', '${picNameList['schoolInfo' + item.index]}', ${item.index} `;
            } else {
                addschoolInfoSql += ` SELECT ${id} , '${item.schoolEduction}', '${item.schoolName}', '${item.schoolSpecial}', '${item.graduationTime}', '${item.schoolStyle}', '${picNameList['schoolInfo' + item.index]}', ${item.index} UNION `;
            }
        });
        await sql.query(addschoolInfoSql);
    }
    if (lastResult.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
  

}




// 获取所有员工信息
const getEntryRegistrationInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let searchList = getBody.searchList;
    let totalCountSql = `SELECT COUNT(D.id) AS TOTAL FROM YG_DA D `;
    let selectEntrySql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) AS PX,id,username,part,phone,idNum,wageCrrection,realWage FROM YG_DA D `;
    if (searchList.username) {
        selectEntrySql += ` WHERE (D.username LIKE '%${searchList.username}%' OR D.phone LIKE '%${searchList.username}%') `;
        totalCountSql += ` WHERE (D.username LIKE '%${searchList.username}%' OR D.phone LIKE '%${searchList.username}%') `;
    }
    if (0 in searchList.part) {
        selectEntrySql += selectEntrySql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectEntrySql += ` D.job LIKE '%${searchList.part.join(',')}%' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.job LIKE '%${searchList.part.join(',')}%' `;
    }
    if (searchList.idNum) {
        selectEntrySql += selectEntrySql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectEntrySql += ` D.idnum LIKE '%${searchList.idNum}%' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.idnum LIKE '%${searchList.idNum}%' `;
    }
    if (searchList.entryTime) {
        selectEntrySql += selectEntrySql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectEntrySql += ` D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
    }
    selectEntrySql += `) AS T1 WHERE T1.PX BETWEEN ${(getBody.pageIndex - 1) * (getBody.pageSize) + 1} AND ${getBody.pageIndex * (getBody.pageSize)} `;
    let result = await sql.query(selectEntrySql);
    let total = await sql.query(totalCountSql);
    for (let item of result.recordset) {

        let partResult = await sql.query(`SELECT STUFF((SELECT '-'+name FROM GSGL WHERE id in(${item.part}) FOR XML PATH('')),1,1,'') AS part`);
        item.partName = partResult.recordset[0].part;
    };
    ctx.body = { result: result.recordset, total: total.recordset[0].TOTAL };
}

// 获取拼接的公司，部门的名称
const getJoinName = async (val) => {
    let companyResult = await sql.query(`SELECT STUFF((SELECT '-'+name FROM GSGL WHERE id in(${val}) FOR XML PATH('')),1,1,'') AS company`);
    return companyResult.recordset[0].company;
}
// 编辑时获取员工所有的信息
const getEditEntryInfo = async (ctx, next) => {
    let id = ctx.request.body.id;
    let userInfo = await sql.query(`SELECT * FROM YG_DA WHERE id  = ${id}`);
    let schoolInfo = await sql.query(`SELECT * FROM YG_XL WHERE id  = ${id}`);
    let skillInfo = await sql.query(`SELECT * FROM YG_ZCJL WHERE id  = ${id}`);
    let memberInfo = await sql.query(`SELECT * FROM YG_JTCR WHERE id  = ${id}`);
    let addressInfo = await sql.query(`SELECT * FROM YG_DZ WHERE id  = ${id}`);
    let userInfoResult = userInfo.recordset[0];
    userInfoResult.companyName = await getJoinName(userInfoResult.company);
    userInfoResult.partName = await getJoinName(userInfoResult.part);
    userInfoResult.postName = await getJoinName(userInfoResult.post);
    userInfoResult.jobName = await getJoinName(userInfoResult.job);
    ctx.body = {
        userInfo: userInfoResult,
        schoolInfo: schoolInfo.recordset,
        skillInfo: skillInfo.recordset,
        memberInfo: memberInfo.recordset,
        addressInfo: addressInfo.recordset[0],
    }
}
// 获取员工证件照片
const getEntryPicAdress = async (ctx, next) => {
    let id = ctx.request.body.id;
    let selectBasePicResult = await sql.query(`SELECT registPhoto,identityCard  FROM YG_DA WHERE id = ${id}`);
    let selectSchoolPicResult = await sql.query(`SELECT idPhoto FROM YG_XL WHERE id = ${id}`);
    let picList = [];
    picList.push(selectBasePicResult.recordset[0].registPhoto);
    picList.push(selectBasePicResult.recordset[0].identityCard);
    selectSchoolPicResult.recordset.forEach(item => {
        picList.push(item.idPhoto);
    });
    ctx.body = {
        result: picList
    }
}

//  提交晋升申请人申请的信息
const addEmployeeUpInfo = async (ctx, next) => {
    let body = ctx.request.body;
    let employeeUpList = body.employeeUpList;
    let addSql = `INSERT INTO YG_JS (userId,money,company,part,post,job,riseTime,watchTime,reason,applyTime,status) VALUES (${body.userId},'${employeeUpList.money}','${employeeUpList.company.join(',')}','${employeeUpList.part.join(',')}','${employeeUpList.post.join(',')}','${employeeUpList.job.join(',')}','${employeeUpList.riseTime}','${employeeUpList.watchTime}','${employeeUpList.reason}','${baseFn.getNowFormatDate()}','F')`;
    let result = await sql.query(addSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}

//  提交加薪申请人申请的信息
const addPayRise = async (ctx, next) => {
    let body = ctx.request.body;
    let payRise = body.payRise;
    let addSql = `INSERT INTO YG_JX (userId,money,precent,reason,riseTime,applyTime,status) VALUES (${body.userId},'${payRise.money}','${payRise.precent}','${payRise.reason}','${payRise.riseTime}','${baseFn.getNowFormatDate()}','F')`;
    let result = await sql.query(addSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}


//  提交请假申请人申请的信息
const addApplyLeave = async (ctx, next) => {
    const files = ctx.request.files.proveFile;
    const body = ctx.request.body;
    let applyLeave = JSON.parse(body.applyLeave);
    let provePic = storagePic(files, '')
    // let addSql = `INSERT INTO YG_QJJL (userId,part,post,type,days,startTime,reason,endTime,provePic,applyTime,status,sellingOffStatus) VALUES (${body.userId},'${applyLeave.part}','${applyLeave.post}','${applyLeave.type}','${applyLeave.days}','${applyLeave.leaveTime[0]}','${applyLeave.reason}','${applyLeave.leaveTime[1]}','${provePic}','${baseFn.getNowFormatDate()}','F','F')`;
    let addSql = `INSERT INTO YG_QJJL (userId,type,days,startTime,reason,endTime,provePic,applyTime,status,sellingOffStatus) VALUES (${body.userId},'${applyLeave.type}','${applyLeave.days}','${applyLeave.leaveTime[0]}','${applyLeave.reason}','${applyLeave.leaveTime[1]}','${provePic}','${baseFn.getNowFormatDate()}','F','F')`;

    let result = await sql.query(addSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}

//  提交转正申请人申请的信息
const addTurnFormal = async (ctx, next) => {
    const files = ctx.request.files.picFile;
    const body = ctx.request.body;
    let turnFormal = JSON.parse(body.turnFormal);
    let picName = storagePic(files, '')
    let addSql = `INSERT INTO YG_ZZ (userId,turnTime,picName,applyTime,status) VALUES (${body.userId},'${turnFormal.turnTime}','${picName}','${baseFn.getNowFormatDate()}','F')`;
    let result = await sql.query(addSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}

// 获取下拉选项公司的信息
const getSelectCompanyInfor = async (ctx, next) => {
    const body = ctx.request.body;
    let selectSql = `SELECT id AS value,name AS label,type,pid,category FROM GSGL WHERE category = 1 AND status = 'T'`;
    let result = await sql.query(selectSql);
    ctx.body = { result: result.recordset };
}
// 获取下拉选项子分类的信息
const getSelectChildInfor = async (ctx, next) => {
    const body = ctx.request.body;
    let category = await sql.query(`SELECT category FROM GSGL WHERE id = ${body.id} `);
    let selectSql = `SELECT id AS value,name AS label,type,pid,category FROM GSGL WHERE pid = ${body.id} AND status = 'T' AND category = ${category.recordset[0].category + 1}`;
    let result = await sql.query(selectSql);
    ctx.body = { result: result.recordset };
}
// 获取所有的职务
const getAllJob = async (ctx, next) => {
    let result = await sql.query(`SELECT id AS value,name AS label,type,pid,category FROM GSGL WHERE category = 4`);
    ctx.body = { result: result.recordset };
}
module.exports = {
    'POST /keepEntryRegistration': keepEntryRegistration,
    'POST /getEntryRegistrationInfo': getEntryRegistrationInfo,
    'POST /getEditEntryInfo': getEditEntryInfo,
    'POST /getEntryPicAdress': getEntryPicAdress,
    'POST /addEmployeeUpInfo': addEmployeeUpInfo,
    'POST /addPayRise': addPayRise,
    'POST /addTurnFormal': addTurnFormal,
    'POST /addApplyLeave': addApplyLeave,
    'POST /getSelectCompanyInfor': getSelectCompanyInfor,
    'POST /getSelectChildInfor': getSelectChildInfor,
    'GET /getUnAuditCount': getUnAuditCount,
    'GET /getAllJob': getAllJob



}